package com.aplink.generic.database;

import java.util.ArrayList;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.text.TextUtils;

import com.aplink.generic.pojo.ContentData;
import com.aplink.generic.pojo.GenericAttribute;
import com.aplink.generic.pojo.GenericObject;
import com.aplink.generic.pojo.ObjPropInfo;
import com.aplink.generic.sharepreference.GenericSharePreference;
import com.aplink.generic.util.LogUtil;

public class CoreDatabaseHelper {
	private static final String DELETE_STATUS = "delete";
	private static final int GET_ALL = -1;
	private static final String INSERT_STATUS = "insert";
	private static final String NULL = null;
	private static final String UPDATE_STATUS = "update";
	private final SQLiteDatabase mDatabase;

	public CoreDatabaseHelper(final SQLiteDatabase database) {
		this.mDatabase = database;
	}

	private String buildWhereCondition(final ContentData objPropCol,
	        final GenericObject genericObject) {
		final String table = objPropCol.getMainTable();
		final ObjPropInfo createdTime = objPropCol.getCreatedTime(table);
		final SqlMethods sqlMethods = SqlMethods.getInstance();
		String primaryKeyValues = null;
		if (createdTime != null) {
			primaryKeyValues = genericObject.getValue(createdTime
			        .getFullColumnName());
		}
		if ((createdTime == null) || TextUtils.isEmpty(primaryKeyValues)) {
			final ObjPropInfo primaryKey = objPropCol.getPrimaryKey(table);
			if (!TextUtils.isEmpty(genericObject.getValue(primaryKey
			        .getFullColumnName()))) {
				final String value = genericObject.getValue(
				        primaryKey.getFullColumnName()).toString();
				sqlMethods.equal(primaryKey, value);
			}
		} else {
			sqlMethods.equal(createdTime, primaryKeyValues);
		}
		return sqlMethods.toString();
	}

	public String deleteData(final ContentData objPropCol,
	        final GenericObject genericObject) {
		final String mainTable = objPropCol.getMainTable();
		handleSyncStatus(objPropCol.getSyncStatus(mainTable), genericObject,
		        CoreDatabaseHelper.DELETE_STATUS);
		final String whereClause = buildWhereCondition(objPropCol,
		        genericObject);
		long newRowID = -1;
		final ContentValues contentValues = getContentValuesSyncStatus(
		        objPropCol, genericObject);
		if (GenericSharePreference.getInstance().isOfflineMode()
		        && (contentValues != null)) {
			newRowID = this.mDatabase.update(mainTable, contentValues,
			        whereClause, null);
		} else {
			newRowID = this.mDatabase.delete(mainTable, whereClause, null);
		}
		String realRowID = null;
		if (newRowID != -1) {
			final ObjPropInfo primaryKey = objPropCol.getPrimaryKey(mainTable);
			realRowID = genericObject.getValue(primaryKey.getFullColumnName())
			        .toString();
		}
		return realRowID;
	}

	private ContentValues getContentValues(final ContentData objPropCol,
	        final GenericObject genericObject) {
		final ContentValues contentValues = new ContentValues();
		for (int i = 0; i < objPropCol.size(); i++) {
			final ObjPropInfo column = objPropCol.valueAt(i);
			if (column.isKey() && column.isAuto()) {
				continue;
			}
			if (!column.isSave()) {
				continue;
			}
			final String columnName = column.getColumnName();
			GenericAttribute attribute = genericObject.get(column
			        .getFullColumnName());
			if ((attribute == null) || (attribute.getValue() == null)) {
				if (column.isKey()) {
					final ObjPropInfo createdTime = objPropCol
					        .getCreatedTime(genericObject.getTableName());
					if (createdTime != null) {
						attribute = genericObject.get(createdTime
						        .getFullColumnName());
						if (attribute.getValue() != null) {
							contentValues.put(columnName, attribute.getValue()
							        .toString());
						}
					}
				} else if (column.isFKey()) {
					attribute = genericObject.get(column
					        .getForeignFullColumnName());
					if ((attribute == null) || (attribute.getValue() == null)) {
						contentValues.putNull(columnName);
					} else {
						contentValues.put(columnName, attribute.getValue()
						        .toString());
					}
				}
			} else {
				contentValues.put(columnName, attribute.getValue().toString());
			}
		}
		return contentValues;
	}

	private ContentValues getContentValuesSyncStatus(
	        final ContentData objPropCol, final GenericObject genericObject) {
		final ContentValues contentValues = new ContentValues();
		final ObjPropInfo column = objPropCol.getSyncStatus(objPropCol
		        .getMainTable());
		if (column != null) {
			final String columnName = column.getColumnName();
			final GenericAttribute attribute = genericObject.get(column
			        .getFullColumnName());
			if ((attribute == null) || (attribute.getValue() == null)) {
				contentValues.putNull(columnName);
			} else {
				final String value = attribute.getValue().toString();
				contentValues.put(columnName, value);
			}
			return contentValues;
		}
		return null;
	}

	public ArrayList<GenericObject> getData(final ContentData objPropCol,
	        String whereClause, final int limitRecord) {
		final ArrayList<GenericObject> genericObjectList = new ArrayList<GenericObject>();
		final String tableName = objPropCol.getMainTable();
		if (TextUtils.isEmpty(tableName)) {
			return genericObjectList;
		}
		final ArrayList<String> queriedColumns = objPropCol.getQueriedColumns();
		final ArrayList<ObjPropInfo> selectedColumn = objPropCol
		        .getSelectedColumn();

		final StringBuilder syncStatus = new StringBuilder();
		for (int i = 0; i < objPropCol.getSyncStatus().size(); i++) {
			final ObjPropInfo info = objPropCol.getSyncStatus().get(i);
			if ((i != 0) && (i < objPropCol.getSyncStatus().size())) {
				syncStatus.append(" AND ");
			}
			syncStatus.append(String.format("%s is not 'delete'",
			        info.getFullColumnName()));
		}

		for (final String condition : objPropCol.getDataRules()) {
			if (TextUtils.isEmpty(whereClause)) {
				whereClause = condition;
			} else {
				whereClause += " AND " + condition;
			}
		}

		if (!syncStatus.toString().isEmpty()) {
			if (whereClause != null) {
				whereClause += " AND " + syncStatus.toString();
			} else {
				whereClause = syncStatus.toString();
			}
		}

		final String limit = limitRecord != CoreDatabaseHelper.GET_ALL ? String
		        .valueOf(limitRecord) : null;
		String queryString;
		if (objPropCol.getJoins().isEmpty()) {
			queryString = SQLiteQueryBuilder.buildQueryString(false, tableName,
			        queriedColumns.toArray(new String[queriedColumns.size()]),
			        whereClause, objPropCol.getGroupBy(), null,
			        objPropCol.getOderBy(), limit);
		} else {
			queryString = SQLiteQueryBuilder.buildQueryString(false,
			        objPropCol.getJoins(),
			        queriedColumns.toArray(new String[queriedColumns.size()]),
			        whereClause, objPropCol.getGroupBy(), null,
			        objPropCol.getOderBy(), limit);
		}
		Cursor cursor = this.mDatabase.rawQuery(queryString, null);
		LogUtil.info(LogUtil.TAG_DATABASE, queryString, "buildQueryString",
		        getClass());
		ObjPropInfo longitudeCol = objPropCol.getLongitudeCol();
		ObjPropInfo latgitude = objPropCol.getLatitudeCol();
		if (cursor != null) {
			cursor.moveToFirst();
			final int numRows = cursor.getCount();
			for (int i = 0; i < numRows; ++i) {
				final GenericObject genericObject = new GenericObject();
				genericObject.setLatitudeCol(latgitude);
				genericObject.setLongitudeCol(longitudeCol);
				genericObject.setTableName(tableName);
				for (int j = 0; j < selectedColumn.size(); j++) {
					final ObjPropInfo objPropInfo = selectedColumn.get(j);
					final String aliasColumnName = objPropInfo
					        .getAliasColumnName();
					final int columnIndex = cursor
					        .getColumnIndex(aliasColumnName);
					final String value = cursor.getString(columnIndex);
					final GenericAttribute attribute = GenericAttribute.obtain(
					        value, objPropInfo);
					genericObject.put(objPropInfo.getFullColumnName(),
					        attribute);
				}
				genericObjectList.add(genericObject);
				cursor.moveToNext();
			}
			cursor.close();
			cursor = null;
		}
		return genericObjectList;
	}

	public ArrayList<GenericObject> getData(final ContentData objPropCol,
	        String whereClause, final int limitRecord,
	        final boolean isCheckDataRule) {
		final ArrayList<GenericObject> genericObjectList = new ArrayList<GenericObject>();
		final String tableName = objPropCol.getMainTable();
		if (TextUtils.isEmpty(tableName)) {
			return genericObjectList;
		}
		final ArrayList<String> queriedColumns = objPropCol.getQueriedColumns();
		final ArrayList<ObjPropInfo> selectedColumn = objPropCol
		        .getSelectedColumn();

		final StringBuilder syncStatus = new StringBuilder();
		for (int i = 0; i < objPropCol.getSyncStatus().size(); i++) {
			final ObjPropInfo info = objPropCol.getSyncStatus().get(i);
			if ((i != 0) && (i < objPropCol.getSyncStatus().size())) {
				syncStatus.append(" AND ");
			}
			syncStatus.append(String.format("%s is not 'delete'",
			        info.getFullColumnName()));
		}

		if (isCheckDataRule) {
			for (final String condition : objPropCol.getDataRules()) {
				if (TextUtils.isEmpty(whereClause)) {
					whereClause = condition;
				} else {
					whereClause += " AND " + condition;
				}
			}
		}

		if (!syncStatus.toString().isEmpty()) {
			if (whereClause != null) {
				whereClause += " AND " + syncStatus.toString();
			} else {
				whereClause = syncStatus.toString();
			}
		}

		final String limit = limitRecord != CoreDatabaseHelper.GET_ALL ? String
		        .valueOf(limitRecord) : null;
		Cursor cursor;
		String queryString;
		if (objPropCol.getJoins().isEmpty()) {
			queryString = SQLiteQueryBuilder.buildQueryString(false, tableName,
			        queriedColumns.toArray(new String[queriedColumns.size()]),
			        whereClause, objPropCol.getGroupBy(), null,
			        objPropCol.getOderBy(), limit);
		} else {
			queryString = SQLiteQueryBuilder.buildQueryString(false,
			        objPropCol.getJoins(),
			        queriedColumns.toArray(new String[queriedColumns.size()]),
			        whereClause, objPropCol.getGroupBy(), null,
			        objPropCol.getOderBy(), limit);
		}
		cursor = this.mDatabase.rawQuery(queryString, null);
		LogUtil.info(LogUtil.TAG_DATABASE, queryString, "buildQueryString",
		        getClass());
		if (cursor != null) {
			cursor.moveToFirst();
			final int numRows = cursor.getCount();
			for (int i = 0; i < numRows; ++i) {
				final GenericObject genericObject = new GenericObject();
				genericObject.setTableName(tableName);
				for (int j = 0; j < selectedColumn.size(); j++) {
					final ObjPropInfo objPropInfo = selectedColumn.get(j);
					final String aliasColumnName = objPropInfo
					        .getAliasColumnName();
					final int columnIndex = cursor
					        .getColumnIndex(aliasColumnName);
					final String value = cursor.getString(columnIndex);
					final GenericAttribute attribute = GenericAttribute.obtain(
					        value, objPropInfo);
					genericObject.put(objPropInfo.getFullColumnName(),
					        attribute);
				}
				genericObjectList.add(genericObject);
				cursor.moveToNext();
			}
			cursor.close();
			cursor = null;
		}
		return genericObjectList;
	}

	private void handleSyncStatus(final ObjPropInfo objPropInfo,
	        final GenericObject genericObject, final String action) {
		if (objPropInfo != null) {
			final String columnName = objPropInfo.getFullColumnName();
			if (GenericSharePreference.getInstance().isOfflineMode()) {
				GenericSharePreference.getInstance().setDatabaseModified(true);
				final String syncStatusValue = genericObject
				        .getValue(columnName) == null ? null : genericObject
				        .getValue(columnName).toString();
				if (TextUtils.isEmpty(syncStatusValue)) {
					genericObject.put(columnName, action);
				} else {
					if (action
					        .equalsIgnoreCase(CoreDatabaseHelper.UPDATE_STATUS)) {
						if (syncStatusValue
						        .equalsIgnoreCase(CoreDatabaseHelper.INSERT_STATUS)) {
							genericObject.put(columnName,
							        CoreDatabaseHelper.INSERT_STATUS);
						} else {
							genericObject.put(columnName,
							        CoreDatabaseHelper.UPDATE_STATUS);
						}
					} else if (action
					        .equalsIgnoreCase(CoreDatabaseHelper.DELETE_STATUS)) {
						genericObject.put(columnName,
						        CoreDatabaseHelper.DELETE_STATUS);
					} else {
						genericObject.put(columnName,
						        CoreDatabaseHelper.INSERT_STATUS);
					}
				}
			} else {
				genericObject.put(columnName, CoreDatabaseHelper.NULL);
			}
		}
	}

	public String insertData(final ContentData objPropCol,
	        final GenericObject genericObject) {
		final String mainTable = objPropCol.getMainTable();
		handleSyncStatus(objPropCol.getSyncStatus(mainTable), genericObject,
		        CoreDatabaseHelper.INSERT_STATUS);
		final ContentValues contentValues = getContentValues(objPropCol,
		        genericObject);
		final long newRowID = this.mDatabase.insertWithOnConflict(mainTable,
		        null, contentValues, SQLiteDatabase.CONFLICT_REPLACE);
		String realRowID = null;
		if (newRowID != -1) {
			final ObjPropInfo primaryKey = objPropCol.getPrimaryKey(mainTable);
			if (primaryKey.isAuto()) {
				realRowID = String.valueOf(newRowID);
				genericObject.put(primaryKey.getFullColumnName(), realRowID);
			} else {
				GenericAttribute attribute = genericObject.get(primaryKey
				        .getFullColumnName());
				if ((attribute == null) || (attribute.getValue() == null)) {
					final ObjPropInfo createdTime = objPropCol
					        .getCreatedTime(genericObject.getTableName());
					if (createdTime != null) {
						attribute = genericObject.get(createdTime
						        .getFullColumnName());
						realRowID = attribute.getValue().toString();
					}
				} else {
					realRowID = genericObject.getValue(
					        primaryKey.getFullColumnName()).toString();
				}
			}
		}
		return realRowID;
	}

	public boolean isExists(final ContentData objPropCol,
	        final String whereClause) {
		final SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
		if (TextUtils.isEmpty(objPropCol.getJoins())) {
			queryBuilder.setTables(objPropCol.getMainTable());
		} else {
			queryBuilder.setTables(objPropCol.getJoins());
		}
		queryBuilder.appendWhere(whereClause);
		final Cursor cursor = queryBuilder.query(this.mDatabase, null, null,
		        null, null, null, null, "1");
		return cursor.getCount() == 1;
	}

	public boolean isExists(final GenericObject genericObject,
	        final ContentData objPropCol) {
		final String whereCondition = buildWhereCondition(objPropCol,
		        genericObject);
		final SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
		if (TextUtils.isEmpty(objPropCol.getJoins())) {
			queryBuilder.setTables(objPropCol.getMainTable());
		} else {
			queryBuilder.setTables(objPropCol.getJoins());
		}
		queryBuilder.appendWhere(whereCondition);
		final Cursor cursor = queryBuilder.query(this.mDatabase, null, null,
		        null, null, null, null, "1");
		return cursor.getCount() == 1;
	}

	public String updateData(final ContentData objPropCol,
	        final GenericObject genericObject) {
		final String mainTable = objPropCol.getMainTable();
		handleSyncStatus(objPropCol.getSyncStatus(mainTable), genericObject,
		        CoreDatabaseHelper.UPDATE_STATUS);
		final ContentValues contentValues = getContentValues(objPropCol,
		        genericObject);
		final String whereClause = buildWhereCondition(objPropCol,
		        genericObject);
		final long newRowID = this.mDatabase.update(mainTable, contentValues,
		        whereClause, null);
		String realRowID = null;
		if (newRowID != -1) {
			final ObjPropInfo primaryKey = objPropCol.getPrimaryKey(mainTable);
			realRowID = genericObject.getValue(primaryKey.getFullColumnName())
			        .toString();
		}
		return realRowID;
	}
}
